'''
Created on May 19, 2009

@author: mkiyer
'''

from elixir import metadata, session, setup_all, create_all, Entity, Field, String, Integer, BLOB
from elixir.options import using_options

import gzip
import os.path

__db_path = '/home/mkiyer/projects/geneDB/genedb.sqlite'
__refflat_path = '/home/mkiyer/projects/geneDB/refFlat.txt.gz'

class Gene(Entity):
    '''
    Table representing the RefSeq gene annotations from the UCSC genome browser
    
    The table is available at http://genome.ucsc.edu as refFlat.txt.gz
    
    Explanation of database fields:
    geneName     NLRP8    varchar(255)     values     Name of gene as it appears in genome browser.
    name     NM_176811    varchar(255)     values     Name of gene
    chrom     chr19    varchar(255)     values     Reference sequence chromosome or scaffold
    strand     +    char(1)     values     + or - for strand
    txStart     61151009    int(10) unsigned     range     Transcription start position
    txEnd     61191807    int(10) unsigned     range     Transcription end position
    cdsStart     61151080    int(10) unsigned     range     Coding region start
    cdsEnd     61191091    int(10) unsigned     range     Coding region end
    exonCount     10    int(10) unsigned     range     Number of exons
    exonStarts     61151009,61155715,61157678,...    longblob           Exon start positions
    exonEnds     61151447,61155790,61159278,...    longblob           Exon end positions
    '''
    # set the table name
    using_options(tablename="genes")

    # table fields
    geneName = Field(String(255), required=True)
    nmName = Field(String(255), required=True)
    chrom = Field(String(255), required=True)
    strand = Field(String(1), required=True)
    txStart = Field(Integer, required=True)
    txEnd = Field(Integer, required=True)
    cdsStart = Field(Integer, required=True)
    cdsEnd = Field(Integer, required=True)
    exonCount = Field(Integer, required=True)
    exonStarts = Field(BLOB, required=True)
    exonEnds = Field(BLOB, required=True)

    def __repr__(self):
        return '<Gene "%s %s %s %s %s %s>' % (self.geneName, self.nmName, self.chrom, self.strand, self.txStart, self.txEnd)

def build_gene_db(path_to_refflat, path_to_db):
    '''
    constructs a RefSeq gene database
    
    path_to_refflat: file path to the file refFlat.tar.gz containing the RefSeq gene table
    path_to_db: path to the database to be created (currently sqlite)
    '''
    # bind to database
    metadata.bind = "sqlite:///" + path_to_db
    metadata.bind.echo = True
    # build database
    setup_all()
    create_all()

    # insert genes into db
    refflat_fhd = gzip.open(path_to_refflat)
    for thisline in refflat_fhd:
        fields = thisline.strip().split('\t')
        Gene(geneName=fields[0],
             nmName=fields[1], 
             chrom=fields[2], 
             strand=fields[3], 
             txStart=fields[4], 
             txEnd=fields[5],
             cdsStart=fields[6], 
             cdsEnd=fields[7], 
             exonCount=fields[8], 
             exonStarts=fields[9], 
             exonEnds=fields[10])
    session.commit()

def test_insert_gene():
    # insert a gene
    Gene(geneName="mygene", nmName="NM_00001", chrom="chr1", strand="+", txStart="10", txEnd="50",
         cdsStart="", cdsEnd="", exonCount="1", exonStarts="10", exonEnds="50")
    session.commit()

if __name__ == "__main__":
    # TODO: automatically download the refFlat table from UCSC
    build_gene_db(__refflat_path, __db_path)